unit dorm.MappingCreator.Strategy.ADO.SqlServer9;

interface

uses
  Classes, dorm.MappingCreator.Strategy,dorm.adapter.ADO.Factory,
  Generics.Collections, DBXJSON, dorm.Configuration,
  {$IF CompilerVersion > 22}
  Data.DB, Data.Win.ADODB,
  {$ELSE}
  AdoDb,
  {$IFEND}
  superobject; // used only to format json... we should replace it!

type
  TADOSqlServer9MappingCreatorStrategy = class(TMappingCreatorStrategy)
  protected
    SS9: TADOFactory;
    function GetTablesName: TList<string>; override;
    function CreatePKFieldMapping(TableName: String): TJSONObject;
      override;
    function CreateFieldsMapping(TableName: String): TJSONArray;
      override;
    procedure InitUserParameters; override;
    procedure Connect; override;
    procedure Disconnect; override;
    function InternalExecute(AOutputStream: TStream): Boolean; override;
  public
    const
    CONNECTION_STRING = 'connection_string';
    PACKAGE = 'package';
    constructor Create; override;
    destructor Destroy; override;
  end;

implementation

uses
  SysUtils;

const
  GET_TABLES_AND_VIEWS = 'select name from sys.tables where type=''U'' ';

  GET_TABLE_INFO = 'select c.name as field,'+
                   'CASE c.user_type_id '+
                   'WHEN ''34''  THEN ''IMAGE''     '+
                   'WHEN ''35''  THEN ''TEXT''     '+
                   'WHEN ''36''  THEN ''UNIQUEIDENTIFIER''     '+
                   'WHEN ''38''  THEN ''INTEGER''  '+
                   'WHEN ''39''  THEN ''VARCHAR''  '+
                   'WHEN ''47''  THEN ''47''  '+
                   'WHEN ''48''  THEN ''TINYINT''  '+
                   'WHEN ''50''  THEN ''50''  '+
                   'WHEN ''52''  THEN ''SMALLINT''  '+
                   'WHEN ''55''  THEN ''AUTOINC''  '+
                   'WHEN ''56''  THEN ''INT''      '+
                   'WHEN ''58''  THEN ''SMALLDATETIME''      '+
                   'WHEN ''59''  THEN ''REAL''      '+
                   'WHEN ''60''  THEN ''MONEY''  '+
                   'WHEN ''61''  THEN ''DATETIME''  '+
                   'WHEN ''62''  THEN ''FLOAT''  '+
                   'WHEN ''98''  THEN ''SQL_VARIANT''  '+
                   'WHEN ''99''  THEN ''NTEXT''  '+
                   'WHEN ''104'' THEN ''BIT''  '+
                   'WHEN ''106'' THEN ''DECIMAL''  '+
                   'WHEN ''108'' THEN ''NUMERIC''  '+
                   'WHEN ''122'' THEN ''SMALLMONEY''    '+
                   'WHEN ''127'' THEN ''BIGINT''    '+
                   'WHEN ''165'' THEN ''VARBINARY'' '+
                   'WHEN ''167'' THEN ''VARCHAR'' '+
                   'WHEN ''173'' THEN ''BINARY'' '+
                   'WHEN ''175'' THEN ''CHAR'' '+
                   'WHEN ''189'' THEN ''TIMESTAMP'' '+
                   'WHEN ''231'' THEN ''NVARCHAR'' '+
                   'WHEN ''239'' THEN ''NCHAR'' '+
                   'END as type, '+
                   ' c.max_length as length,'+
                   ' c.precision as precision,'+
                   ' c.scale as scale,'+
                   ' c.is_nullable as nullable '+
                   ' from sys.columns c'+
                   ' inner join sys.tables t'+
                   ' on c.object_id=t.object_id'+
                   ' where t.name=''%s'' and c.is_identity = %s and t.type=''U''';

procedure TADOSqlServer9MappingCreatorStrategy.Connect;
begin
  inherited;
  SS9 := TADOFactory.Create(nil, GetUserParameterByName(CONNECTION_STRING));
end;

constructor TADOSqlServer9MappingCreatorStrategy.Create;
begin
  inherited;
end;

function TADOSqlServer9MappingCreatorStrategy.CreateFieldsMapping(
  TableName: String): TJSONArray;

  function GetFieldMapping(AQuery: TADOQuery): TJSONObject;
  var
    RawFieldType: String;
    dormFieldType: string;
    Obj: TJSONObject;
    RawFieldName: string;
  begin
    RawFieldType := Trim(AQuery.FieldByName('Type').AsString);
    RawFieldName := Trim(AQuery.FieldByName('Field').AsString);
    if (RawFieldType = 'INTEGER') or
       (RawFieldType = 'INT') or
       (RawFieldType = 'SMALLINT') or
       (RawFieldType = 'TINYINT') or
       (RawFieldType = 'BIGINT') then dormFieldType := 'integer'
    else if (RawFieldType = 'DECIMAL') then
      dormFieldType := 'decimal'
    //else if (RawFieldType = 'FLOAT') then
    //  dormFieldType := 'decimal'
    //else if (RawFieldType = 'NUMERIC') then
    //  dormFieldType := 'decimal'
    //else if (RawFieldType = 'REAL') then
    //  dormFieldType := 'decimal'
    //else if (RawFieldType = 'SMALLMONEY') or
    //        (RawFieldType = 'MONEY') then dormFieldType := 'currency'
    else if (RawFieldType = 'VARCHAR') or
            (RawFieldType = 'CHAR') then dormFieldType := 'string'
    else if (RawFieldType = 'NVARCHAR') or
            (RawFieldType = 'NCHAR') then dormFieldType := 'string'
    else if (RawFieldType = 'SMALLDATETIME') then dormFieldType := 'date'
    else if (RawFieldType = 'DATETIME') or
            (RawFieldType = 'TIMESTAMP') then dormFieldType := 'datetime'
    else if (RawFieldType = 'IMAGE') or
            (RawFieldType = 'TEXT') or
            (RawFieldType = 'NTEXT') or
            (RawFieldType = 'VARBINARY') or
            (RawFieldType = 'BINARY') then dormFieldType := 'blob'
    else if (RawFieldType = 'UNIQUEIDENTIFIER') then dormFieldType := 'guid'
    else
    begin
      raise Exception.Create('Cannot map field ' + TableName + '.' +
        AQuery.FieldByName('Field').AsString + ' of type ' + RawFieldType);
    end;

    // {"name":"Age", "field":"AGE", "field_type":"integer"},
    // {"name":"FirstName", "field":"FIRST_NAME", "field_type":"string", "size": 50},

    Obj := TJSONObject.Create;

    if dormFieldType = 'string' then
      Obj.AddPair(TJSONPair.Create('size',
        TJSONNumber.Create(AQuery.FieldByName('Length').AsInteger)));

    if dormFieldType = 'decimal' then
    begin
      Obj.AddPair('size',
        TJSONNumber.Create(AQuery.FieldByName('Precision').AsInteger))
        .AddPair('precision',
        TJSONNumber.Create(AQuery.FieldByName('Scale').AsInteger));
    end;

    Obj
      .AddPair('name',
      FieldNameToClassAtribute(RawFieldName))
      .AddPair('field', Trim(AQuery.FieldByName('Field').AsString))
      .AddPair('field_type', dormFieldType);

    Result := Obj;
  end;

var
  Query: TADOQuery;
begin
  Result := TJSONArray.Create;
  Query := SS9.GetQuery;
  Query.Sql.Clear;
  Query.Sql.Add(Format(GET_TABLE_INFO, [TableName,'0']));
  try
    Query.Open;
    while not Query.Eof do
    begin
      Result.AddElement(GetFieldMapping(Query));
      Query.Next;
    end;
    Query.Close;
  finally
    //Query.Free;
  end;
end;

function TADOSqlServer9MappingCreatorStrategy.CreatePKFieldMapping(
  TableName: String): TJSONObject;
var
  Query: TADOQuery;
  RawFieldType: String;
  dormFieldType: string;
  RawFieldName: string;
  ThisClassAlreadyHaveAnOID: Boolean;
begin
  Result := nil;
  ThisClassAlreadyHaveAnOID := false;
  Query := SS9.GetQuery;
  Query.Sql.Clear;
  Query.Sql.Add(Format(GET_TABLE_INFO, [TableName,'1']));
  try
    Query.Open;
    while not Query.Eof do
    begin
      { todo: Check the case with compound key }
      // What happend if there is a compound key?
      if ThisClassAlreadyHaveAnOID then
        raise Exception.Create('Not supported! Table ' + TableName +
          ' have a compound key');
      ThisClassAlreadyHaveAnOID := true;
      Result := TJSONObject.Create;
      RawFieldType := Trim(Query.FieldByName('Type').AsString);
      RawFieldName := Trim(Query.FieldByName('Field').AsString);

      if (RawFieldType = 'INTEGER') or
         (RawFieldType = 'INT') or
         (RawFieldType = 'SMALLINT') or
         (RawFieldType = 'TINYINT') or
         (RawFieldType = 'BIGINT') then dormFieldType := 'integer'
      else if (RawFieldType = 'DECIMAL') then
        dormFieldType := 'decimal'
      else if (RawFieldType = 'VARCHAR') then
        dormFieldType := 'string'
      else if (RawFieldType = 'UNIQUEIDENTIFIER') then
        dormFieldType := 'guif'
      else
      begin
        raise Exception.Create('Cannot map field ' + TableName + '.' +
          RawFieldName + ' of type ' + RawFieldType + ' as primary key');
      end;

      Result
        .AddPair('name', FieldNameToClassAtribute(RawFieldName))
        .AddPair('field', RawFieldName)
        .AddPair('field_type', dormFieldType);

      Query.Next;
    end;
    Query.Close;
  finally
    //Query.Free;
  end;
end;

destructor TADOSqlServer9MappingCreatorStrategy.Destroy;
begin
  if Assigned(FConfiguration) then
    FConfiguration.Free;
  inherited;
end;

procedure TADOSqlServer9MappingCreatorStrategy.Disconnect;
begin
  inherited;
  FreeAndNil(SS9);
end;

function TADOSqlServer9MappingCreatorStrategy.GetTablesName: TList<string>;
var
  Query: TADOQuery;
begin
  Query := SS9.GetQuery;
  Query.Sql.Clear;
  Query.Sql.Add(GET_TABLES_AND_VIEWS);
  try
    Result := TList<string>.Create;
    Query.Open;
    while not Query.Eof do
    begin
      Result.Add(Trim(Query.FieldByName('NAME').AsString));
      Query.Next;
    end;
    Query.Close;
  finally
    //Query.Free;
  end;
end;

procedure TADOSqlServer9MappingCreatorStrategy.InitUserParameters;
begin
  {$IFDEF DEBUG}
  AddParameter(CONNECTION_STRING, 'Provider=SQLOLEDB.1;Password=querydev;Persist Security Info=True;User ID=sa;Initial Catalog=dorm_test;Data Source=qcmarmotta\sqlexpress');
  {$ELSE}
  AddParameter(CONNECTION_STRING, 'Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Initial Catalog=dorm_test;Data Source=SERVERTEST\TEST');
  {$ENDIF}
  AddParameter(PACKAGE, 'BusinessObjects');
end;

function TADOSqlServer9MappingCreatorStrategy.InternalExecute(AOutputStream
  : TStream): Boolean;
var
  Tables: TList<string>;
  TableMapping, json: TJSONObject;
  TableName: String;
  sw: TStreamWriter;
  sojson: ISuperObject;
begin
  Result := true;
  try
    json := TJSONObject.Create;
    try
      Tables := GetTablesName;
      try
        for TableName in Tables do
        begin
          TableMapping := TJSONObject.Create;
          json.AddPair(TableNameToClassName(TableName), TableMapping);
          TableMapping
            .AddPair('package', GetUserParameterByName(PACKAGE))
            .AddPair('table', TableName)
            .AddPair('id', CreatePKFieldMapping(TableName))
            .AddPair('fields', CreateFieldsMapping(TableName))
            .AddPair('has_many', TJSONArray.Create)
            .AddPair('has_one', TJSONArray.Create)
            .AddPair('belongs_to', TJSONArray.Create);
        end;
      finally
        Tables.Free;
      end;
      sw := TStreamWriter.Create(AOutputStream);
      try
        sojson := SO(json.ToString);
        sw.Write(sojson.AsJSon(true));
        sojson := nil;
      finally
        sw.Free;
      end;
    finally
      json.Free;
    end;
  except
    on E: Exception do
    begin
      Errors.Add(E.ClassName + ': ' + E.Message);
      Result := false;
    end;
  end;
end;

initialization

TMappingCreatorStrategyRegister.Register
  (TADOSqlServer9MappingCreatorStrategy,
  'ADOSqlServer9');

end.
